﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Runda.CMS.Common.Entity;
using Runda.CMS.Dal;
using Runda.CMS.Common.Utility;
using System.Transactions;
using System.Data;
using System.Data.Common;

namespace Runda.CMS.Bll
{
    public class BudgetBll:BllBase
    {
        

        //公司配件库存
        public DataTable QueryAccessoryStockByCo(string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new StockDal().QueryAccessoryStockByCo(WhereClause, OrderBy, PageSize, PageIndex, out RecordCount);
        } 

        public DataTable CalcAccessoryStock(string WhereClause)
        {
            return new StockDal().CalcAccessoryStock(WhereClause);
        }
        
        //通过物品识别号获得所在库存ID
        public string getStockid(string accessoryCode)
        {
            string sql="select stockid from Cms_Wsm_AccessoryStock where AccessoryCode='"+accessoryCode+"'";
            DataTable dt = new DataTable();
            dt = new CommonBll().getDataFromSQL(sql);
            return dt.Rows[0]["stockid"].ToString();
        }

        public DataTable ExportAccessoryStock(string sqlwhere, string OrderBy)
        {
            string sql = @"select AccessoryTypeName as [类型]
                        ,CompanyInstrumentCode as [编号（公司）]
                        ,AccessoryName as [名称]
                        ,FactoryInstrumentCode as [厂家代码]
                        ,AccessoryStandard as [规格]
                        ,AccessoryCode as [识别号]
                        ,AccessoryStatusName as [所处位置]
                        ,ReceiverName as [领用人]
                        ,InStockdate as [入库日期] 
                        ,OutStockDate as [申领日期]
                        ,AccessoryUsedDate as [使用日期]
                        from vw_StockDetail
                        where AccessoryCode is not null {0} order by {1} ";
            sql = string.Format(sql, sqlwhere, OrderBy);
            DataTable dt = new DataTable();
            dt = new CommonBll().getDataFromSQL(sql);
            return dt;
        }


        public void SaveSalesBudgets(BGSalesBudgetsEntity entity, List<BGSalesBudgetsByMonthEntity> list)
        {
            using (System.Transactions.TransactionScope tran = new TransactionScope())
            {
                this.InsertOrUpdate(entity);

                if (list != null)
                {
                    this.Delete<BGSalesBudgetsByMonthEntity>(" and BudgetID = '" +entity.BudgetID + "'");

                    foreach (BGSalesBudgetsByMonthEntity ent in list)
                    {
                        this.Insert(ent);
                    }
                }

                tran.Complete();
            }

        }

        /// <summary>
        /// 更新折扣分摊
        /// </summary>
        /// <param name="items"></param>
        public void UpdateSalesBills(BGSalesBillsEntity entity, List<BGSalesBillsEntity> items)
        {
            using (System.Transactions.TransactionScope tran = new TransactionScope())
            {
                this.Update(entity);
                foreach (BGSalesBillsEntity item in items)
                {
                    this.Update(item);
                }
                tran.Complete();
            }
        }

        public void UpdateSalesBills(List<BGSalesBillsEntity> items)
        {
            using (System.Transactions.TransactionScope tran = new TransactionScope())
            {
                foreach (BGSalesBillsEntity item in items)
                {
                    this.Update(item);
                }
                tran.Complete();
            }
        }

        public DataTable QueryInventoryPutPlan_Rpt(string WhereClause,string Orderby)
        {
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return dal.QueryInventoryPutPlan_Rpt(WhereClause,Orderby);
        }
        public DataTable QueryCustomerSalesBudget_Rpt(string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return dal.QueryCustomerSalesBudget_Rpt(WhereClause, OrderBy, PageSize, PageIndex, out RecordCount);
   
        }

        public DataTable QueryCustomer2SalesBudget_Rpt(string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return dal.QueryCustomer2SalesBudget_Rpt(WhereClause, OrderBy, PageSize, PageIndex, out RecordCount);

        }


        public DataTable ExportInventoryPutPlan_Rpt(string WhereClause, string Orderby)
        {
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return dal.ExportInventoryPutPlan_Rpt(WhereClause, Orderby);
        }

        public DataTable ExportCustomerSalesBudget_Rpt(string WhereClause)
        {
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return dal.ExportCustomerSalesBudget_Rpt(WhereClause);
        }

        public DataTable GetSalesBudgets(string WhereClause)
        {
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return dal.GetSalesBudgets(WhereClause);
        }

        public DataTable QuerySalesBudgets(string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return dal.QuerySalesBudgets(WhereClause, OrderBy, PageSize, PageIndex, out RecordCount);
        }

        public void UpdateByAssistID(BGSalesBudgetsEntity entity)
        {
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            dal.UpdateByAssistID(entity);
        }

        public void SubmmitBudget(string bgYear, string Operator)
        {
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            dal.SubmmitBudget(bgYear, Operator);
        }
        public void RejectBudget(string ProcessID)
        {
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            dal.RejectBudget(ProcessID);
        }

        public void CheckBudget(string ProcessID, string Operator)
        {
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            dal.CheckBudget(ProcessID, Operator);
        }

        public void ReturnBudget(string ProcessID, string Operator)
        {
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            dal.ReturnBudget(ProcessID, Operator);
        }

        public void DeleteByAssistID(Guid AssistID)
        {
            using (System.Transactions.TransactionScope tran = new TransactionScope())
            {
                this.Delete<BGSalesBudgetsEntity>(" and AssistID='" + AssistID + "'");

                this.Delete<BGSalesBudgetsByMonthEntity>(" and AssistID = '"+AssistID+"'");

                tran.Complete();
            }
        }

        public void DeleteByBudgetID(Guid BudgetID)
        {
            using (System.Transactions.TransactionScope tran = new TransactionScope())
            {
                this.Delete<BGSalesBudgetsEntity>(" and BudgetID='" + BudgetID + "'");

                this.Delete<BGSalesBudgetsByMonthEntity>(" and BudgetID = '" + BudgetID + "'");

                tran.Complete();
            }
        }

        public void DeleteBGSalesBill(Guid billGUID)
        {
            this.Delete<BGSalesBillsEntity>(" and billGUID='" + billGUID + "'");
            
        }


        public DataTable CalcInventoryPutPlan_Rpt(string WhereClause)
        {
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return dal.CalcInventoryPutPlan_Rpt(WhereClause);
        }

        public DataTable CalcSalesBudgets(string WhereClause)
        {
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return dal.CalcSalesBudgets(WhereClause);
        }

        public DataTable CalcCustomerSalesBudget_Rpt(string WhereClause)
        {
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return dal.CalcCustomerSalesBudget_Rpt(WhereClause);
        }

        //销售预算明细
        public DataTable ExportSalesBudgets(string WhereClause, string OrderBy)
        {
            return new BGSalesBudgetsDal().ExportSalesBudgets(WhereClause, OrderBy);
        }

        public int getDataright(string userid)
        {
            string sql = "select 1 from Cms_Sys_UserDepartment_DataRight where UserID = '" + userid + "'";
            return new CommonBll().getDataFromSQL(sql).Rows.Count;
            
        }

        public DataTable Companylist(string userid)
        {
            string sql = @"select d.DepartmentID,d.DepartmentName,d.DepartmentCode 
                from Cms_Sys_UserDepartment_DataRight a 
                inner join Cms_Sys_Department d on d.DepartmentID = a.DepartmentID where d.isCompany = 1 and a.userid='{0}' order by d.DepartmentCode asc";
            string strsql = string.Format(sql,userid);
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return new CommonBll().getDataFromSQL(strsql);

        }

        public DataTable InstrumentPutList(string BudgetYear)
        {
            string sql = @"select distinct InstrumentName 
                from Cms_BG_InstrumentPutList a where a.CostYear ='{0}' order by InstrumentName asc";
            string strsql = string.Format(sql, BudgetYear);
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return new CommonBll().getDataFromSQL(strsql);

        }

        public DataTable Departmentlist(string userid,string companyno)
        {
            string sql = @"with CTE as
            (
            select * from Cms_Sys_Department where DepartmentCode = '{1}'

            union all

            select A.* from Cms_Sys_Department A join CTE on A.ParentID = CTE.DepartmentID
            )
            select distinct d.DepartmentName
                from Cms_Sys_UserDepartment_DataRight a 
                inner join Cms_Sys_Department d on d.DepartmentID = a.DepartmentID 
                where d.isCompany = 0 and a.userid='{0}' and d.DepartmentID in(select DepartmentID from CTE where ParentID is not null)
                order by d.DepartmentName asc";
            string strsql = string.Format(sql, userid, companyno);
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return new CommonBll().getDataFromSQL(strsql);

        }

        public DataTable Departmentlist(string userid)
        {
            string sql = @"select distinct d.DepartmentName
                from Cms_Sys_UserDepartment_DataRight a 
                inner join Cms_Sys_Department d on d.DepartmentID = a.DepartmentID where d.isCompany = 0 and a.userid='{0}' order by d.DepartmentName asc";
            string strsql = string.Format(sql, userid);
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return new CommonBll().getDataFromSQL(strsql);

        }

        public DataTable Saleslist(string userid)
        {
            string sql = @"select userid,username from vw_sysUser where DepartmentID  in (select d.DepartmentID
                from Cms_Sys_UserDepartment_DataRight a 
                inner join Cms_Sys_Department d on d.DepartmentID = a.DepartmentID where d.isCompany = 0 and a.userid='{0}') and UserName  in (select sales from Cms_BG_SalesBudgets)  order by username asc";
            string strsql = string.Format(sql, userid);
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return new CommonBll().getDataFromSQL(strsql);

        }

        public DataTable ProductCategorylist()
        {
            string sql = @"select categoryCode,categoryName from Cms_Ctrt_InstrumentCategory where LEFT(categoryCode,2) = '01' and LEN(categorycode)= 4 order by categorycode";
            
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return new CommonBll().getDataFromSQL(sql);

        }



        public int GetBudgetSubmitStatus(string BudgetYear,string Operator)
        {
            string sql = @"select count(*) 
                from cms_BG_SalesBudgetProcessList where BudgetYear = '{0}' and ProductManager='{1}' and SubmmitStatus=1";
            string strsql = string.Format(sql, BudgetYear, Operator);
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return System.Convert.ToInt16(new CommonBll().getDataFromSQL(strsql).Rows[0][0]);

        }

        public int GetBudgetUnCheckStatus(string BudgetYear)
        {
            string sql = @"select count(*) 
                from cms_BG_SalesBudgetProcessList where BudgetYear = '{0}' and SubmmitStatus = 0";
            string strsql = string.Format(sql, BudgetYear);
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return System.Convert.ToInt16(new CommonBll().getDataFromSQL(strsql).Rows[0][0]);

        }

        public int GetBudgetCheckStatus(string BudgetYear,string ProductManager)
        {
            string sql = @"select count(*) 
                from cms_BG_SalesBudgetProcessList where BudgetYear = '{0}' and ProductManager =  '{1}' and  CheckStatus = 1";
            string strsql = string.Format(sql, BudgetYear, ProductManager);
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return System.Convert.ToInt16(new CommonBll().getDataFromSQL(strsql).Rows[0][0]);

        }

        public List<WsmInstrumentInfoEntity> GetInstrumentEntities(string Code)
        {
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return dal.GetInstrumentEntities(Code);
        }

        public List<WsmInstrumentInfoEntity> GetInstrumentEntities(Guid Code)
        {
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return dal.GetInstrumentEntities(Code);
        }

        public DataTable GenReportData(int type,string sqlwhere)
        {
            DataTable dt;
            string tmpTablename="";
            int rtn;

            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            string sql = "select * from ";
            if (type == 1) { tmpTablename = dal.GenReportDataByProduct(sqlwhere); sql += tmpTablename; }
            if (type == 2) { tmpTablename = dal.GenReportDataBySalesGroup(sqlwhere); sql += tmpTablename; }
            dt = new CommonBll().getDataFromSQL(sql);
            rtn = new CommonDal().DropTable(tmpTablename);
            return dt;
            
        }

        public DataTable QuerySalesBudgetResult(string sqlwhere, string OrderBy)
        {
            string sql = @"select * from cms_BG_SalesBudgetProcessList
                        where 1=1 {0} order by {1} ";
            sql = string.Format(sql, sqlwhere, OrderBy);
            DataTable dt = new DataTable();
            dt = new CommonBll().getDataFromSQL(sql);
            return dt;
        }

        public DataSet GetSalesBudgetRptData_001(string sqlwhere)
        {
            string sql = @"select vd.DepartmentName as SalesGroup,a.sales,c.CustomerName,cc.categoryName
                ,v.m01amount,v.m02amount,v.m03amount,v.m04amount
                ,v.m05amount,v.m06amount,v.m07amount,v.m08amount
                ,v.m09amount,v.m10amount,v.m11amount,v.m12amount 
                ,a.TotalAmount
                ,cc.FactoryName as categoryType
                ,a.SalesBudget1_F
                ,a.SalesBudget1_T
                ,a.Reward1
                ,a.SalesBudget2_F
                ,a.SalesBudget2_T
                ,a.Reward2
                ,a.SalesBudget3_F
                ,a.SalesBudget3_T
                ,a.Reward3
                ,a.SalesBudget4_F
                ,a.SalesBudget4_T
                ,a.Reward4
                ,a.SalesBudget5_F
                ,a.SalesBudget5_T
                ,a.Reward5
                ,a.SalesBudget6_F
                ,a.SalesBudget6_T
                ,a.Fine1
                ,a.SalesBudget7_F
                ,a.SalesBudget7_T
                ,a.Fine2
                ,a.SalesBudget8_F
                ,a.SalesBudget8_T
                ,a.Fine3
                ,a.SalesBudget9_F
                ,a.SalesBudget9_T
                ,a.Fine4
                ,a.SalesBudget10_F
                ,a.SalesBudget10_T
                ,a.Fine5
                from Cms_BG_SalesBudgets a
                inner join vw_sysUser vd on vd.UserName = a.sales
                inner join Cms_Ctrt_Customer c on c.CustomerID = a.customerID
                inner join Cms_Ctrt_InstrumentCategory cc on cc.categoryCode = LEFT(a.productCategoryCode,6)
                inner join vw_SalesBudgetForAmountByMonth v on v.BudgetID = a.BudgetID
                        where 1=1 {0} ";
            sql = string.Format(sql, sqlwhere);
            return new CommonDal().getSqlData_DS(sql);
        }

        public DataSet GetSalesRptData_101(string sqlwhere)
        {
            string sql = @"select 
        a.BillUnit
        ,a.BudgetUnit
        ,a.SalesGroup
        ,a.Sales
        ,c.CustomerName
        ,cc.FactoryName as categoryType
        ,cc.categoryName
        ,DATEPART(year,a.dDate) as SalesYear
        ,DATEPART(MONTH,a.dDate) as SalesMonth
        ,a.iNatSum
        from Cms_BG_Bill a
        inner join Cms_Ctrt_Customer c on c.CustomerCode = a.cCusCode and c.company = BillUnit
        inner join Cms_Ctrt_InstrumentCategory cc on cc.categoryCode = LEFT(a.cCategoryCode,6)
                        where 1=1 {0} ";
            sql = string.Format(sql, sqlwhere);
            return new CommonDal().getSqlData_DS(sql);
        }

    }
}
